Excel BI - Excel Challenge 742

excel-challenges

excel-formulas

🔰 finder, friend, refind finder refind List all words along with their Anagrams in different rows.

Published

March 24, 2026

Illustration for Excel BI - Excel Challenge 742

Challenge Description

🔰 finder, friend, refind finder refind List all words along with their Anagrams in different rows. If a word has no Anagram, don’t list it.

Solutions

library(tidyverse)
library(readxl)

input = read_excel("Excel/700-799/742/742 Anagram Listing.xlsx", range = "A1:A40")
test  = read_excel("Excel/700-799/742/742 Anagram Listing.xlsx", range = "B1:B11")

result = input %>%
  mutate(key = str_split(Words, "") %>% map_chr(~ paste(sort(.x), collapse = ""))) %>%
  filter(n() > 1, .by = key) %>%
  summarise(`Answer Expected` = paste(sort(Words), collapse = ", "), .by = key) %>%
  arrange(`Answer Expected`) %>%
  select(`Answer Expected`)

all.equal(result, test, check.attributes = FALSE)

Logic: Read the workbook ranges needed for the challenge; Derive the required intermediate columns; Parse the packed text or string structure; Aggregate or rank the data at the required grouping level.
Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

import pandas as pd

path = "700-799/742/742 Anagram Listing.xlsx"
input = pd.read_excel(path, usecols="A", nrows=40)
test  = pd.read_excel(path, usecols="B", nrows=10)

result = (input.assign(k=input.iloc[:,0].map(lambda x: ''.join(sorted(str(x)))))
    .groupby('k').filter(lambda g: len(g)>1)
    .groupby('k').agg(lambda x: ', '.join(sorted(x)))
    .reset_index(drop=True)
    .rename(columns={input.columns[0]:'Answer Expected'})
    .sort_values('Answer Expected')
    .reset_index(drop=True))


print(result.equals(test))

The Python version follows the same grouped logic and keeps the transformation explicit in a dataframe pipeline.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.